﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using famails.common;
using famails.dto;

namespace famails.dao
{
    /// <summary>
    /// Summary description for ArticleDTO
    /// </summary>
    public class ArticleDAO
    {
        public ArticleDAO()
        {

        }
        public DataTable Get2EditByID(int ID)
        {
            SqlCommand cmd = new SqlCommand("SELECT a.*, c.categoryid FROM tblBlog_Articles a inner join tblBlog_Groups g on g.groupid =a.groupid inner join tblBlog_Categories c on c.categoryid = g.categoryid WHERE a.IsDelete=0 and a.ArticleID = @ID", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }


        public DataTable GetByID(int ID)
        {
            SqlCommand cmd = new SqlCommand("SELECT a.*, c.categoryid FROM tblBlog_Articles a inner join tblBlog_Groups g on g.groupid =a.groupid inner join tblBlog_Categories c on c.categoryid = g.categoryid WHERE a.IsShow=1 and a.IsDelete=0 and a.ArticleID = @ID", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public DataTable Search(string keyword, int show, int categoryid, int groupid)
        {
            SqlCommand cmd = new SqlCommand("SP_Article_Search", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@show", SqlDbType.Int).Value = show;
            cmd.Parameters.Add("@keyword", SqlDbType.NVarChar).Value = keyword;
            cmd.Parameters.Add("@GroupId", SqlDbType.Int).Value = groupid;
            cmd.Parameters.Add("@CategoryId", SqlDbType.Int).Value = categoryid;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        public bool Insert(ArticleDTO dto)
        {
            SqlCommand cmd = new SqlCommand("insert into tblBlog_Articles(Title, IsShow, GroupId, Summary, image, Content, CreateDate)values(@Title, @show, @GroupId, @Summary, @image, @Content, getdate())", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@show", SqlDbType.Int).Value = dto.IsShow;
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = dto.Title;
            cmd.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = dto.Summary;
            cmd.Parameters.Add("@Content", SqlDbType.NVarChar).Value = dto.Content;
            cmd.Parameters.Add("@GroupId", SqlDbType.Int).Value = dto.GroupID;
            cmd.Parameters.Add("@Image", SqlDbType.NVarChar).Value = dto.Image;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }

        public bool Edit(ArticleDTO dto)
        {
            SqlCommand cmd = new SqlCommand("update tblBlog_Articles set Title=@Title, IsShow=@Show, GroupId=@GroupId, Summary=@Summary, image=@image, Content=@Content where Articleid = @id", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@show", SqlDbType.Bit).Value = dto.IsShow;
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = dto.Title;
            cmd.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = dto.Summary;
            cmd.Parameters.Add("@Content", SqlDbType.NVarChar).Value = dto.Content;
            cmd.Parameters.Add("@GroupId", SqlDbType.Int).Value = dto.GroupID;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = dto.ArticleID;
            cmd.Parameters.Add("@Image", SqlDbType.NVarChar).Value = dto.Image;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }

        public bool Delete(int id)
        {
            SqlCommand cmd = new SqlCommand("update tblBlog_Articles set isdelete=1 where Articleid = @id", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }

        internal bool CheckExistsName(string name, int id)
        {
            SqlCommand cmd = new SqlCommand("SELECT articleid FROM tblBlog_Articles WHERE Articleid <> @id and title = @name and isdelete=0", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table.Rows.Count > 0;
        }


        internal DataTable GetTopByGroupID(int top, int GroupID)
        {

            SqlCommand cmd = new SqlCommand("SELECT top "+top+" * FROM tblBlog_Articles WHERE isdelete=0 and GroupID = @GroupID and isshow=1 order by createdate desc", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@groupid", SqlDbType.Int).Value = GroupID;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();

            table.Columns.Add(new DataColumn("STT", typeof(int)));
            int i = 1;
            foreach (DataRow row in table.Rows)
            {
                row["STT"] = i;
                i += 1;
            }
            return table;
        }

        internal DataTable GetNewest()
        {
            SqlCommand cmd = new SqlCommand("SELECT top 1 * FROM tblBlog_Articles WHERE isdelete=0 and isshow=1 order by createdate desc", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        internal DataTable GetTop1Read()
        {
            SqlCommand cmd = new SqlCommand("SELECT top 1 * FROM tblBlog_Articles WHERE isdelete=0 and isshow=1 order by viewcount desc", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }

        internal bool VIewCount(int ArticleID)
        {
            SqlCommand cmd = new SqlCommand("update tblBlog_Articles set viewcount=isnull(viewcount,0)+1 where Articleid = @id", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = ArticleID;
            int i = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return i > 0;
        }
    }
}
